﻿using ControlExplorer.samples.DataBinding;
using FarPoint.Web.Spread;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Web.UI.WebControls;

namespace ControlExplorer.samples.Filter
{
  public partial class ExcelFilter : SpreadDemoPage
  {
    protected void Page_Load(object sender, EventArgs e)
    {
      if (IsPostBack)
      {
        return;
      }

      InitSpread(FpSpread1);
      InitSpreadStyles(FpSpread1.Sheets[0]);
      this.FpSpread1.UseClipboard = false;
    }

    private void InitSpread(FarPoint.Web.Spread.FpSpread spread)
    {
      FpSpread1.DataSource = GetOrders();
      spread.CssClass = "spreadStyle";
      spread.UseClipboard = false;
    }

    private void InitSpreadStyles(FarPoint.Web.Spread.SheetView sheet)
    {
      sheet.DefaultStyle.VerticalAlign = VerticalAlign.Middle;
      sheet.ColumnCount = 8;

      sheet.Columns[0].Width = 60;
      sheet.Columns[1].Width = 200;
      sheet.Columns[2].Width = 150;
      sheet.Columns[3].Width = 100;
      sheet.Columns[4].Width = 100;
      sheet.Columns[5].Width = 100;
      sheet.Columns[6].Width = 100;
      sheet.Columns[7].Width = 200;

      DateTimeCellType dt = new DateTimeCellType();
      dt.FormatString = "MM/dd/yyyy";
      sheet.Columns[4].CellType = dt;
      sheet.Columns[5].CellType = dt;

      Dictionary<string, Color> employees = new Dictionary<string, Color>();
      employees["Sales Manager"] = Color.Red;
      employees["Sales Representative"] = Color.DarkGreen;
      employees["Inside Sales Coordinator"] = Color.DarkViolet;
      employees["Vice President, Sales"] = Color.DarkOrange;

      for (int i = 0; i < sheet.RowCount; i++)
      {
        string employee = sheet.Cells[i, 2].Text;
        Color color = Color.Black;
        employees.TryGetValue(employee, out color);
        sheet.Cells[i, 2].ForeColor = color;
      }

      sheet.DefaultStyle.VerticalAlign = VerticalAlign.Middle;

      sheet.AutoFilterMode = FarPoint.Web.Spread.AutoFilterMode.Enhanced;
      FarPoint.Web.Spread.IRowFilter rowFilter = new FarPoint.Web.Spread.HideRowFilter(sheet);

      // add a FilterColumnDefinition for Columns[3]
      FarPoint.Web.Spread.FilterColumnDefinition fd0 = new FarPoint.Web.Spread.FilterColumnDefinition(2, FarPoint.Web.Spread.FilterListBehavior.Default);
      FarPoint.Web.Spread.FilterColumnDefinition fd3 = new FarPoint.Web.Spread.FilterColumnDefinition(3, FarPoint.Web.Spread.FilterListBehavior.Default);
      FarPoint.Web.Spread.FilterColumnDefinition fd4 = new FarPoint.Web.Spread.FilterColumnDefinition(4, FarPoint.Web.Spread.FilterListBehavior.Default);
      FarPoint.Web.Spread.FilterColumnDefinition fd5 = new FarPoint.Web.Spread.FilterColumnDefinition(5, FarPoint.Web.Spread.FilterListBehavior.Default);
      FarPoint.Web.Spread.FilterColumnDefinition fd6 = new FarPoint.Web.Spread.FilterColumnDefinition(6, FarPoint.Web.Spread.FilterListBehavior.Default);
      rowFilter.ColumnDefinitions.Add(fd0);
      rowFilter.ColumnDefinitions.Add(fd3);
      rowFilter.ColumnDefinitions.Add(fd4);
      rowFilter.ColumnDefinitions.Add(fd5);
      rowFilter.ColumnDefinitions.Add(fd6);

      sheet.RowFilter = rowFilter;
    }

    DataSet GetOrders()
    {
      string constr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Northwind.mdb;Persist Security Info=True";

      string sqlstr =
@"SELECT TOP 100 
          Orders.OrderID as 订单号, 
          Customers.CompanyName as 顾客, 
          Employees.Title as 雇员, 
          SUM(details.Quantity) as 数量, 
          Orders.RequiredDate as 咨询日期, 
          Orders.ShippedDate as 发货日期, 
          Orders.ShipCity as 发货城市, 
          Orders.ShipAddress as 发货地址
  FROM ((Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID) 
        INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID) 
  INNER JOIN [Order Details] as details ON Orders.OrderID = details.OrderID 
  GROUP BY  Orders.OrderID, Customers.CompanyName, Employees.Title, Orders.RequiredDate, 
            Orders.ShippedDate, Orders.ShipCity, Orders.ShipAddress";

      DataSet orders = new DataSet();
      using (OleDbConnection myconn = new OleDbConnection(constr))
      {
        OleDbDataAdapter sqlAdapter1 = new OleDbDataAdapter(sqlstr, myconn);
        sqlAdapter1.Fill(orders, "Orders");
      }

      return orders;
    }
  }
}